package quanlydoanvao.bctuan;

import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;

import javax.faces.model.SelectItem;

import dbutil.ConnectDB;
import exportExcel.ExportExcel;

import quanlydoanra.baocao.dinhky.Month;
import utils.DateUtils;
import utils.UtilsOnScopes;

public class BeanBCTuan {
	private String error;
	private String tuan;
	private String thang;
	private String nam;
	private List<TableDisplay> list;
	private String bieumau="/qldoanvao/baocaotuan/emptyBieuMau.xhtml";
	private String tongsoNguoi;
	private String tongSoDoanVao;
	private String tongSoNguoiTrongDoan;
	private String pathDownload;
	private ArrayList<HashMap<String, Object>> database = new ArrayList<HashMap<String,Object>>();
	private StringBuilder dsnguoidi;
	private StringBuilder dsquoctich;
	private StringBuilder dshochieu;
	private StringBuilder dsgioitinh;
	
	
	public StringBuilder getDsnguoidi() {
		return dsnguoidi;
	}

	public void setDsnguoidi(StringBuilder dsnguoidi) {
		this.dsnguoidi = dsnguoidi;
	}

	public StringBuilder getDsquoctich() {
		return dsquoctich;
	}

	public void setDsquoctich(StringBuilder dsquoctich) {
		this.dsquoctich = dsquoctich;
	}

	public StringBuilder getDshochieu() {
		return dshochieu;
	}

	public void setDshochieu(StringBuilder dshochieu) {
		this.dshochieu = dshochieu;
	}

	public StringBuilder getDsgioitinh() {
		return dsgioitinh;
	}

	public void setDsgioitinh(StringBuilder dsgioitinh) {
		this.dsgioitinh = dsgioitinh;
	}

	public ArrayList<HashMap<String, Object>> getDatabase() {
		return database;
	}

	public void setDatabase(ArrayList<HashMap<String, Object>> database) {
		this.database = database;
	}

	public String getPathDownload() {
		return pathDownload;
	}

	public void setPathDownload(String pathDownload) {
		this.pathDownload = pathDownload;
	}

	/****************************************************
	 * Method name		: getTongSoNguoiTrongDoan [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getTongSoNguoiTrongDoan() {
		return tongSoNguoiTrongDoan;
	}

	/****************************************************
	 * Method name		: setTongSoNguoiTrongDoan [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setTongSoNguoiTrongDoan(String tongSoNguoiTrongDoan) {
		this.tongSoNguoiTrongDoan = tongSoNguoiTrongDoan;
	}

	/****************************************************
	 * Method name		: getTongsoNguoi [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getTongsoNguoi() {
		return tongsoNguoi;
	}

	/****************************************************
	 * Method name		: setTongsoNguoi [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setTongsoNguoi(String tongsoNguoi) {
		this.tongsoNguoi = tongsoNguoi;
	}

	/****************************************************
	 * Method name		: getTongSoDoanVao [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getTongSoDoanVao() {
		return tongSoDoanVao;
	}

	/****************************************************
	 * Method name		: setTongSoDoanVao [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setTongSoDoanVao(String tongSoDoanVao) {
		this.tongSoDoanVao = tongSoDoanVao;
	}

	/****************************************************
	 * Method name		: getBieumau [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getBieumau() {
		return bieumau;
	}

	/****************************************************
	 * Method name		: setBieumau [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 30, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setBieumau(String bieumau) {
		this.bieumau = bieumau;
	}

	/****************************************************
	 * Method name		: getList [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public List<TableDisplay> getList() {
		return list;
	}

	/****************************************************
	 * Method name		: setList [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setList(List<TableDisplay> list) {
		this.list = list;
	}

	/****************************************************
	 * Method name		: getError [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getError() {
		return error;
	}

	/****************************************************
	 * Method name		: setError [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setError(String error) {
		this.error = error;
	}

	/****************************************************
	 * Method name		: getTuan [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getTuan() {
		return tuan;
	}

	/****************************************************
	 * Method name		: setTuan [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setTuan(String tuan) {
		this.tuan = tuan;
	}

	/****************************************************
	 * Method name		: getThang [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getThang() {
		return thang;
	}

	/****************************************************
	 * Method name		: setThang [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setThang(String thang) {
		this.thang = thang;
	}

	/****************************************************
	 * Method name		: getNam [GETTER]
	 * Return type		: BeanBCTuan
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public String getNam() {
		return nam;
	}

	/****************************************************
	 * Method name		: setNam [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 ****************************************************/
	public void setNam(String nam) {
		this.nam = nam;
	}

	/**************************************
	 * Method name		: getMonth
	 * Return type		: List<Year>
	 * Decription		:Lay ra danh sach cac thang
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 **************************************/
	
	private List<Month> getMonth() {
		List<Month> list = new ArrayList<Month>();
		int beginMonth = 1;
		while (beginMonth <= 12) {
			Month month = new Month();
			month.setValue(String.valueOf(beginMonth));
			month.setLable(String.valueOf("Tháng "+beginMonth));
			list.add(month);
			beginMonth += 1;
		}return list;
	}
	
	/**************************************
	 * Method name		: listItemMonth
	 * Return type		: SelectItem[]
	 * Decription		:Lay ra cac item select cua thang
	 * Created date		: Jul 13, 2008
	 * Author			: Honglk
	 **************************************/
	public SelectItem[] listItemMonth() {
		List<Month> li = this.getMonth();
		SelectItem[] arrItem = new SelectItem[li.size() + 1];
		arrItem[0] = new SelectItem("-1", "---Chọn---");
		for (int i = 0; i < li.size(); i++) {
			Month month = li.get(i);
			arrItem[i + 1] = new SelectItem(month.getValue(), month.getLable());
		}
		return arrItem;
	}
	
	public SelectItem[] getListItemMonth() {
		return this.listItemMonth();
	}
	
	/**************************************
	 * Method name		: getWeek
	 * Return type		: List<Year>
	 * Decription		:Lay ra danh sach cac thang
	 * Created date		: Jul 29, 2008
	 * Author			: Honglk
	 **************************************/
	
	private List<Tuan> getWeek() {
		List<Tuan> list = new ArrayList<Tuan>();
		Tuan tuan1 = new Tuan("1","Tuần I");
		Tuan tuan2 = new Tuan("2","Tuần II");
		Tuan tuan3 = new Tuan("3","Tuần III");
		Tuan tuan4 = new Tuan("4","Tuần IV");
		list.add(tuan1);
		list.add(tuan2);
		list.add(tuan3);
		list.add(tuan4);
		return list;
	}
	
	/**************************************
	 * Method name		: listItemMonth
	 * Return type		: SelectItem[]
	 * Decription		:Lay ra cac item select cua thang
	 * Created date		: Jul 13, 2008
	 * Author			: Honglk
	 **************************************/
	public SelectItem[] listItemWeek() {
		List<Tuan> li = this.getWeek();
		SelectItem[] arrItem = new SelectItem[li.size() + 1];
		arrItem[0] = new SelectItem("-1", "---Chọn---");
		for (int i = 0; i < li.size(); i++) {
			Tuan tuan = li.get(i);
			arrItem[i + 1] = new SelectItem(tuan.getValue(), tuan.getLable());
		}
		return arrItem;
	}
	public SelectItem[] getListItemWeek() {
		return this.listItemWeek();
	}
	
	
	private String filtData(String nam,String thang,String tuan){
		String result ="";
		int tuanInt = Integer.parseInt(tuan==null?"0":tuan);
		switch (tuanInt) {
		case 1:
			String dateStartStrTuan1 = nam +"-"+ thang+"-"+DateUtils.ngayBatDauTrongTuan("1");
			String dateEndStrTuan1 = nam +"-"+ thang+"-"+DateUtils.ngayKetThucTrongTuan("1");
			Date dateStartSQLTuan1 = Date.valueOf(dateStartStrTuan1);
			Date dateEndSQLTuan1 = Date.valueOf(dateEndStrTuan1);
			result = "A.ngayden BETWEEN '" +dateStartSQLTuan1 +
			 					"' AND '" +dateEndSQLTuan1+"'";
			break;
		case 2:
			String dateStartStrTuan2 = nam +"-"+ thang+"-"+DateUtils.ngayBatDauTrongTuan("2");
			String dateEndStrTuan2 = nam +"-"+ thang+"-"+DateUtils.ngayKetThucTrongTuan("2");
			Date dateStartSQLTuan2 = Date.valueOf(dateStartStrTuan2);
			Date dateEndSQLTuan2= Date.valueOf(dateEndStrTuan2);
			result = "A.ngayden BETWEEN '" +dateStartSQLTuan2 +
			 					"' AND '" +dateEndSQLTuan2+"'";
			break;
		case 3:
			String dateStartStrTuan3 = nam +"-"+ thang+"-"+DateUtils.ngayBatDauTrongTuan("3");
			String dateEndStrTuan3 = nam +"-"+ thang+"-"+DateUtils.ngayKetThucTrongTuan("3");
			Date dateStartSQLTuan3 = Date.valueOf(dateStartStrTuan3);
			Date dateEndSQLTuan3 = Date.valueOf(dateEndStrTuan3);
			result = "A.ngayden BETWEEN '" +dateStartSQLTuan3 +
			 					"' AND '" +dateEndSQLTuan3+"'";
			break;
		case 4:
			String dateStartStrTuan4 = nam +"-"+ thang+"-"+DateUtils.ngayBatDauTrongTuan("4");
			String dateEndStrTuan4 = nam +"-"+ thang+"-"+DateUtils.ngayKetThucTrongTuan("4");
			Date dateStartSQLTua4 = Date.valueOf(dateStartStrTuan4);
			Date dateEndSQLTuan4 = Date.valueOf(dateEndStrTuan4);
			result = "A.ngayden BETWEEN '" +dateStartSQLTua4 +
			 					"' AND '" +dateEndSQLTuan4+"'";
			break;
			
		default:
			
			break;
		}
		return result;
	}
	
	public String tables() {
		List<TableDisplay> list = new ArrayList<TableDisplay>();
		String cond=this.filtData(this.nam, this.thang,this.tuan);
		String SQL = "select DISTINCT E.sovb,E.coquandenghi,E.ngayxuatvb,E.noidung,A.diadiemlamviec," +
				" A.ngayden,C.linhvuc,D.soubndtinh,D.ngayphathanh,A.madoan " +
					  " from  doanvao A inner join dsnguoiden B on A.madoan =B.iddoanvao " +
		              " inner join mucdich C on A.idmucdich = C.id " +
		              " inner join vanbannhan D on A.idvbvao = D.id " +
		              " inner join vanbanden E on E.id = A.idvbden " +
		              " WHERE " + cond;
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB
				.getDataTypeList(SQL);
		
	  // danh cho bao cao(Xuat ra file Excel)
		this.setTongSoDoanVao("<b>Tổng số đoàn vào: "+String.valueOf(array.size() +"</b>"));
		for (HashMap<String, Object> row : array) {
			this.getMemberList((String) row.get("madoan"));
			row.put("nguoitrongdoan", this.getDsnguoidi().toString());
			row.put("dssohc", this.getDshochieu().toString());
			row.put("dsquoctich", this.getDsquoctich().toString());
			row.put("dsgioitinh", this.getDsgioitinh().toString());
			
			row.put("slthanhvientrongdoan", this.tongSoNguoiTrongDoan(row.get("madoan").toString()));
			String ngayxuatvb = (row.get("ngayxuatvb")== null?"":row.get("ngayxuatvb").toString());
			row.put("ngayxuatvanban", DateUtils.formatDate(ngayxuatvb));
			String ngayden = (row.get("ngayden")== null?"":row.get("ngayden").toString());
			row.put("ngaydenlamviec", DateUtils.formatDate(ngayden));
			String ngayphatvb = (row.get("ngayphathanh")== null?"":row.get("ngayphathanh").toString());
			row.put("ngayphathanhvb", DateUtils.formatDate(ngayphatvb));
		}
		this.database = array;
		int intTongNguoi = 0;
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				int stt = 0;
				for (int i = 0; i < array.size(); i++) {
					hash = (HashMap<String, Object>) array.get(i);
					intTongNguoi += Integer.parseInt(this.tongSoNguoiTrongDoan(hash.get("madoan").toString()));
					stt = i + 1;
					TableDisplay table = new TableDisplay();
					table.setStt(String.valueOf(stt));
					table.setSovb(hash.get("sovb")==null?"":hash.get("sovb").toString());
					table.setCoquandenghi(hash.get("coquandenghi")== null?"":hash.get("coquandenghi").toString());
					String ngayxuatvb = (hash.get("ngayxuatvb")== null?"":hash.get("ngayxuatvb").toString());
					table.setNgayxuatvb(DateUtils.formatDate(ngayxuatvb));
					table.setNoidung(hash.get("noidung")==null?"":hash.get("noidung").toString());
					table.setDiadiemlamviec(hash.get("diadiemlamviec")==null?"":hash.get("diadiemlamviec").toString());
					String ngayden = (hash.get("ngayden")== null?"":hash.get("ngayden").toString());
					table.setNgayden(DateUtils.formatDate(ngayden));
					table.setLinhvuc(hash.get("linhvuc")==null?"":hash.get("linhvuc").toString());
					table.setSoubndtinh(hash.get("soubndtinh")==null?"":hash.get("soubndtinh").toString());
					String ngayphatvb = (hash.get("ngayphathanh")== null?"":hash.get("ngayphathanh").toString());
					table.setNgayphathanh(DateUtils.formatDate(ngayphatvb));
					table.setSohc(hash.get("dssohc")==null?"":hash.get("dssohc").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
					table.setDsThanhVien((hash.get("nguoitrongdoan").toString()).replaceAll(System.getProperty("line.separator"),"<br/>"));
					table.setQuoctich(hash.get("dsquoctich")==null?"":hash.get("dsquoctich").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
					table.setGioitinh(hash.get("dsgioitinh")==null?"":hash.get("dsgioitinh").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
					table.setSlThanhVienTrongDoan(this.tongSoNguoiTrongDoan(hash.get("madoan").toString()));
					list.add(table);
				}
			}else{
				list = new ArrayList<TableDisplay>();
				this.setError("Không có dữ liệu !");
			}
			String strTongNguoi = "<b>Tổng số: " +String.valueOf(intTongNguoi) +" Người</b>";
			this.setTongsoNguoi(strTongNguoi);
			this.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	public ArrayList<HashMap<String, Object>> convertListToHashtable(){
		this.tables();
		List<TableDisplay> li =  this.getList();
		ArrayList<HashMap<String, Object>> arr = new ArrayList<HashMap<String,Object>>();
		for (TableDisplay item : li) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			hash.put("coquandenghi", item.getCoquandenghi());
			hash.put("diadiemlamviec", item.getDiadiemlamviec());
			hash.put("nguoitrongdoan", item.getDsThanhVien().trim().replaceAll("<br/>",System.getProperty("line.separator")));
			hash.put("dsgioitinh", item.getGioitinh().trim().replaceAll("<br/>",System.getProperty("line.separator")));
			hash.put("linhvuc", item.getLinhvuc());
			hash.put("ngayden", item.getNgayden());
			hash.put("ngayphathanh", item.getNgayphathanh());
			hash.put("ngayxuatvb", item.getNgayxuatvb());
			hash.put("noidung", item.getNoidung());
			hash.put("dsquoctich", item.getQuoctich().trim().replaceAll("<br/>",System.getProperty("line.separator")));
			hash.put("slthanhvientrongdoan", item.getSlThanhVienTrongDoan());
			hash.put("dssohc", item.getSohc().trim().replaceAll("<br/>",System.getProperty("line.separator")));
			hash.put("sovb", item.getSovb());
			hash.put("soubndtinh", item.getSoubndtinh());
			arr.add(hash);
		}
		return arr;
	}
	
	public String view(){
		this.setError("");
		this.tables();
		if (this.checkError() == false) {
			return "";
		} else if (this.getList() != null && this.getList().size() > 0) {
			this.setBieumau("/qldoanvao/baocaotuan/bieumau15cot.xhtml");
		}
		return "okBaoCaoTuan";
	}
	
	public boolean checkError() {
		if (this.tuan == null || this.tuan.equals("-1")) {
			this.setError("Bạn chưa chọn tuần !");
			return false;
		} else if (this.thang == null || this.thang.equals("-1")) {
			this.setError("Bạn chưa chọn tháng !");
			return false;
		} else if (this.nam == null || this.nam.equals("-1")) {
			this.setError("Bạn chưa chọn năm !");
			return false;
		}

		return true;
	}
	
	private void getMemberList(String idDoanVao) {
		try {
			String SQL = "SELECT DISTINCT A.ten,A.quoctich, A.sohc,A.gioitinh  FROM dsnguoiden A, doanvao B WHERE A.iddoanvao = B.madoan AND B.madoan = '" + idDoanVao+"'";
			ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
			if(arr.size() > 0){
				StringBuilder dsnguoidi = new StringBuilder();
				StringBuilder dsquoctich = new StringBuilder();
				StringBuilder dshochieu = new StringBuilder();
				StringBuilder dsgioitinh = new StringBuilder();
				for(int i = 0; i < arr.size(); i++){
					HashMap<String, Object> row = arr.get(i);
					String quotich = row.get("quoctich") == null?"":((String)row.get("quoctich")).trim();
					String hochieu = row.get("sohc") == null?"":((String)row.get("sohc")).trim();
					String gioitinh = row.get("gioitinh") == null?"":((String)row.get("gioitinh")).trim();
					String nguoidi = row.get("ten") == null?"":((String)row.get("ten")).trim();
					
					dsnguoidi.append( nguoidi+ System.getProperty("line.separator"));
					dsquoctich.append(quotich + System.getProperty("line.separator"));
					dshochieu.append(hochieu + System.getProperty("line.separator"));
					dsgioitinh.append(gioitinh + System.getProperty("line.separator"));
				}
				this.setDsgioitinh(dsgioitinh);
				this.setDshochieu(dshochieu);
				this.setDsnguoidi(dsnguoidi);
				this.setDsquoctich(dsquoctich);
			} else {
				return ;
			}

		} catch (Exception e) {
			this.setError("Không thể lấy danh sách người trong đoàn.");
		}
	}
	
	public String tongSoNguoiTrongDoan(String idDoanvao){
		String result ="0";
		String SQL ="SELECT DISTINCT COUNT(*) AS count FROM dsnguoiden B " +
		" WHERE  B.iddoanvao='"+idDoanvao+"'";
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
		if(arr.size() > 0){
			HashMap<String, Object> hash = arr.get(0);
			result = hash.get("count").toString();
		}
		return result;
	}
	
	
	public String reset() {
		UtilsOnScopes.getSession().removeAttribute("beanBCTuan");
		return "okBaoCaoTuan";
	}
	
	/**************************************
	 * Method name		: export
	 * Return type		: String
	 * Decription		:Xuat ra file Excel
	 * Created date		: Jul 26, 2008
	 * Author			: Honglk
	 * Modified			: Phonglt [sua pathDownload]
	 **************************************/
	public String export() {
		this.setError("");
		if(this.checkError()== false){
			return "";
		}
		this.tables();
		//this.database = this.convertListToHashtable();
		if(this.database != null && this.database.size() > 0){
			//Header
		Hashtable<String, String> heading = new Hashtable<String, String>();	
			heading.put("TUAN", this.tuan);
			heading.put("THANG", this.thang);
			heading.put("NAM", this.nam);
			//Footer
			Hashtable<String, String> ending = new Hashtable<String, String>();	
			ending.put("TONGSODOAN",String.valueOf(this.database.size()));
			ending.put("NGAY", String.valueOf(DateUtils.LayNgayHienTai()));
			ending.put("THANG", String.valueOf(DateUtils.LayThangHienTai()));
			ending.put("NAM", String.valueOf(DateUtils.LayNamHienTai()));
		ExportExcel.exportJExcel_HaveGroup("quanlydoanvao_template", "baocaotuan.xml",
				"exp_quanlydoanvao", this.database, UtilsOnScopes.getRequest(), 
				heading, ending);
		this.setPathDownload( ExportExcel.pathDownload);
		this.setBieumau("/qldoanvao/baocaotuan/viewExportExcel.xhtml");
		}
		else{
			this.setError("Không có dữ liệu !");
		}
		return "okBaoCaoTuan";
	}
	
	public static void main(String[] args) {
		String tr = "hong</br>";
		//String trrep = tr.replaceAll("</br>", "");
		System.out.println(tr.replaceAll("</br>", "hghfg"));
		System.out.println("tfh"+System.getProperty("line.separator"));
		
	}
	
}
